-- doang lang

create proc spLayThongTinNguoiTimViecTheoUserID
		@user_id int
as
begin
	select u.USER_ID,USERNAME,Ten,Ho,GioiTinh,NgaySinh,DienThoai,DiDong,DiaChi,MaTinhThanh,Email 
	from USERS u,NGUOITIMVIEC nguoitv 
	where u.USER_ID = nguoitv.USER_ID and u.USER_ID = @user_id;
	
end

create proc spUpdateNguoiTimViec
	@User_id int,
	@Ten nvarchar(50),
	@Ho nvarchar(30),
	@GioiTinh bit,
	@Email nvarchar(100),
	@NgaySinh datetime,
	@DienThoai nvarchar(30),
	@DiDong nvarchar(30),
	@DiaChi nvarchar(100),
	@TinhThanh int,
	@flag nvarchar(3) output
as
begin 
	--declare @USER_ID int
	--select @USER_ID = USER_ID from USERS where USERNAME = @USERNAME
	update NguoiTimViec
	set 
	Ten = @Ten,
	Ho = @Ho,
	GioiTinh = @GioiTinh,
	Email = @Email,
	NgaySinh = @NgaySinh,
	DienThoai = @DienThoai,
	DiDong = @DiDong,
	DiaChi = @DiaChi,
	MaTinhThanh = @TinhThanh
	where  USER_ID = @User_id

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
	--
end



--nha tuyen dung
create proc spLayThongTinNhaTuyendungTheoUserID
		@user_id int
as
begin
	select u.USER_ID,u.Username,Ten,Email,NguoiLienHe,DienThoai,DiDong,Fax,SoTaiKhoan,DiaChi,MaTinhThanh,GioiThieu,Quymo 
	from USERS u,NHATUYENDUNG ntd 
	where u.USER_ID = ntd.USER_ID and u.USER_ID = @user_id;
	
end

create proc spUpdateNhaTuyenDung
	@user_id int,
	@Ten nvarchar(256),
	@GioiThieu nvarchar(max),
	@QuyMo nvarchar(max),
	@Email nvarchar(100),
	@NguoiLienHe nvarchar(100),
	@DienThoai nvarchar(30),
	@DiDong nvarchar(30),
	@Fax nvarchar(30),
	@SoTaiKhoan nvarchar(100),
	@DiaChi nvarchar(100),
	@TinhThanh int,
	@flag nvarchar(3) output
as
begin 
	
	update NhaTuyenDung
	set 
	Ten = @Ten,
	GioiThieu = @GioiThieu,
	QuyMo = @QuyMo,
	Email = @Email,
	NguoiLienHe = @NguoiLienHe,
	DienThoai = @DienThoai,
	DiDong = @DiDong,
	Fax = @Fax,
	SoTaiKhoan = @SoTaiKhoan,
	DiaChi = @DiaChi,
	MaTinhThanh = @TinhThanh
	where  USER_ID = @user_id

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
	--
end


--update password
create proc spUpdatePassword
		@user_id int,
		@NewPassword nvarchar(70),
		@flag nvarchar(3) output
as
begin
	update USERS
	set password = @NewPassword
	where USER_ID = @user_id;

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
	--
end


-- lay username thong qua user_id
create proc spGetUsernamePassWordByUserID
		@user_id int
		
as
begin
	select USERNAME,PASSWORD
	from USERS
	where USER_ID = @user_id;
end




--admin
--/********node*************/
--sua file Mymembership

-- tao user
create proc spCreateUser
	@UserName nvarchar(75),
	@PassWord nvarchar(80),
	@Status smallint,
	@Date datetime,
	@Email nvarchar(100),
	@RoleID int,
	@KiemTraCoChonRole int,
	@KiemTraLaNguoiTimViec int,
	@flag nvarchar(3) output
as
begin
	
	
	if(not exists(select Username from USERS where USERNAME = @UserName))
	begin
		insert into USERS(Status,UserName,PassWord,Date,LasthereDate) values(@Status,@UserName,@PassWord,@Date,null);
		
		--
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
	end

	declare @index int;
	select @index = USER_ID
	from USERS
	where USERNAME = @UserName;
	
	if(@KiemTraLaNguoiTimViec = 0)
	begin
		if(not exists(select USER_ID from NGUOITIMVIEC where USER_ID = @index))
		begin
			insert into NguoiTimViec(ten,ho,gioitinh,email,ngaysinh,dienthoai,didong,ngaydangki,ngaycapnhat,diachi,user_id,matinhthanh) values(null,null,null,@Email,null,null,null ,null,null,null,@index,null);
			--
			if(@@error <> 0)
			begin
				set @flag = '0'
				rollback tran
				return
			end
		
			--
		end	
	end
	else
	begin
		insert into NhaTuyenDung(Ten,NgayDangKi,GioiThieu,QuyMo,Email,NguoiLienHe,DienThoai,DiDong,Fax,SoTaiKhoan,DiaChi,MaTinhThanh,NgayCapNhat,USER_ID,LOGO) values(null,null,null,null,@Email,null,null,null ,null,null,null,null,null,@index,null);
		--
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
	end
	
			
	if (@KiemTraCoChonRole = 1)
	begin
		
		insert into USERSINROLES(USER_ID,ROLEID) values(@index,@RoleID);
		--
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
	end

	set @flag = '1'
end


--update user
create proc spUpdateUser
	@user_id int,
	@PassWord nvarchar(80),
	@Status smallint,
	@Date datetime,
	@Email nvarchar(100),
	@flag nvarchar(3) output
as
begin
	update USERS
	set PASSWORD = @PassWord,
		STATUS = @Status,
		DATE = @Date
	where USER_ID = @user_id;

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
		
	--

	if(exists(select USER_ID from NGUOITIMVIEC where USER_ID = @user_id))
	begin
		update NGUOITIMVIEC
		set EMAIL = @Email
		where USER_ID = @user_id;

		--
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
	end
	else
	begin
		if(exists(select USER_ID from NHATUYENDUNG where USER_ID = @user_id))
		begin
			update NHATUYENDUNG
			set EMAIL = @Email
			where USER_ID = @user_id;

			--
			if(@@error <> 0)
			begin
				set @flag = '0'
				rollback tran
				return
			end
		
			--
		end
	end	

	set @flag = '1'
end


--get all role
create proc spGetAllRole
as
begin 
	select ROLEID,NAME from ROLES;
end

--get all user
create proc spGetAllUser
as
begin 
	select USER_ID,STATUS,USERNAME from USERS;
end


--delete user
create proc spDeleteUser
	@user_id int,
	@flag nvarchar(3) output
as
begin 
	delete from USERSINROLES where USER_ID = @user_id;
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
			
	delete from NGUOITIMVIEC where USER_ID = @user_id;
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	delete from NHATUYENDUNG where USER_ID = @user_id;
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	delete from USERS where USER_ID = @user_id;	
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
	
end


-- lay thong tin user thong qua user_id
create proc spGetUserInfomationByUserID
		@user_id int
		
as
begin
	
	if(exists(select USER_ID from NGUOITIMVIEC where USER_ID = @user_id))
	begin
		select u.USER_ID,STATUS,USERNAME,PASSWORD,DATE,EMAIL
		from USERS u,NGUOITIMVIEC n
		where u.USER_ID = n.USER_ID and u.USER_ID = @user_id;
	end
	else
	begin
		if(exists(select USER_ID from NHATUYENDUNG where USER_ID = @user_id))
		begin
			select u.USER_ID,STATUS,USERNAME,PASSWORD,DATE,EMAIL
			from u USERS,NHATUYENDUNG n
			where u.USER_ID = n.USER_ID and u.USER_ID = @user_id;
		end
		else
		begin
			select USER_ID,STATUS,USERNAME,PASSWORD,DATE
			from USERS
			where USER_ID = @user_id;
		end
	end
end


--get role for user
create proc spGetRoleUser
	@user_id int
as
begin 
	select ur.ROLEID from USERS u,USERSINROLES ur where ur.USER_ID = u.USER_ID AND u.USER_ID = @user_id;
end

--delete All role for user
create proc spDeleteAllRoleUser
	@user_id int,
	@flag nvarchar(3) output
as
begin 
	delete from USERSINROLES where USER_ID = @user_id;

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
		
	--
	
	set @flag = '1'
end


--delete Role for user
create proc spDeleteRoleUser
	@user_id int,
	@roleID int,
	@flag nvarchar(3) output
as
begin 
	delete from USERSINROLES where USER_ID = @user_id and ROLEID = @roleID;

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
		
	--

	set @flag = '1'
end


-- update role for user
create proc spInsertRoleUser
	@user_id int,
	@role_id int,
	@flag nvarchar(3) output
as
begin 
		insert into USERSINROLES(USER_ID,ROLEID) values(@user_id,@role_id);

		--
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
		set @flag = '1'
end

-- insert new role
create proc spInsertRole
	@roleName nvarchar(30),
	@description nvarchar(250),
	@flag nvarchar(3) output
as
begin
	insert into ROLES(NAME,DESCRIPTION) values(@roleName,@description);

	--
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
		
	--
	set @flag = '1'
end

-- get user in role
create proc spGetUserInRole
	@roleID int
as
begin
		select u.USER_ID,u.STATUS,u.USERNAME
		from USERS u,USERSINROLES ur,ROLES r
		where u.USER_ID = ur.USER_ID and ur.ROLEID = r.ROLEID and r.ROLEID = @roleID
end



--------------------------------------------CV--------------------------------------------------
create proc spLayTenNhaTuyenDung
	@MaNhaTuyenDung int,
	@TenNhaTuyenDung nvarchar(50) output
as
begin
	select @TenNhaTuyenDung = Ten 
	from NHATUYENDUNG
	where MaNhaTuyenDung = @MaNhaTuyenDung;
end






create proc spGuiCVOnline
	@userID int,
	@manhatuyendung int,
	@ngayGui datetime,
	@flag nvarchar(3) output
as
begin

	declare @maCV int;
	
	select @maCV = cv.MaCV 
	from NguoiTimViec ntv, CV cv
	where ntv.MaNguoiTimViec = cv.MaNguoiTimViec and ntv.User_ID = @userID;
	
	if(not exists(select * from CV_GUI_NHATUYENDUNG where MaCV = @maCV and MaNhaTuyenDung = @manhatuyendung ))
	begin
		insert into CV_GUI_NHATUYENDUNG values(@manhatuyendung,@maCV,@ngayGui,null);
		
		if(@@error <> 0)
		begin
			set @flag = '0'
			rollback tran
			return
		end
		
		--
		set @flag = '1'
	end
	else
	begin
		set @flag = '2'
	end
	--
	
	
end


create proc spUploadCV
	@Ten nvarchar(256),
	@DuongDan nvarchar(256),
	@flag nvarchar(3)
as
begin
	
	insert into CVUPLOAD(Ten,DuongDan) values(@Ten,@DuongDan);
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
end


create proc spGuiCVUpload
	@userID int,
	@manhatuyendung int
as
begin
	
end



---- cap nhat phi nha tuyen dung ----------------
create proc spLayTatCaPhi
as
begin
	select *
	from PHI;
end


create proc spLayThongTinPhiTheoMaPhi
	@maPhi int
as
begin
	select *
	from PHI
	where MaPhi = @maPhi;
end

create proc spCapNhatPhi
	@maPhi int,
	@tenPhi nvarchar(256),
	@thoiHan int,
	@doiTuong int,
	@doUuTien int,
	@soLuongTin int,
	@Gia numeric(18,2),
	@viTriCaoNhat int,
	@viTriThapNhat int,
	@flag nvarchar(3) output
as
begin
	update PHI
	set Ten = @tenPhi,
		ThoiHan = @thoiHan,
		DoiTuong = @doiTuong,
		DoUuTien = @doUuTien,
		SoLuongTin = @soLuongTin,
		Gia = @Gia,
		ViTriCaoNhat = @viTriCaoNhat,
		ViTriThapNhat = @viTriThapNhat
	where MaPhi = @maPhi;

	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1'
		
end

------------------kiem tra dong phi cua nguoi tim  viec -------------------
create proc spKiemTraDongPhiCuaNguoiTimViec
	@userID int,
	@flag nvarchar(3) output
as
begin
	declare @maNguoiTimViec int
	
	select @maNguoiTimViec = MaNguoiTimViec
	from NGUOITIMVIEC
	where USER_ID = @userID;
	
	declare @daDongPhi int
	
	select @daDongPhi = count(*)
	from NGUOITIMVIEC_PHI
	where MaNguoiTimViec = @maNguoiTimViec and TinhTrang = 1;
	
	if(@daDongPhi = 0)
		set @flag = '0'
	else
		set @flag = '1'
	
end


------------------upload CV gui nha tuyen dung ------------------
create proc spCVUploadGuiNhaTuyenDung
	@userID  int,
	@maNhaTuyenDung int,
	@tenCV nvarchar(256),
	@duongDan nvarchar(256),
	@flag nvarchar(3) output
as
begin
	
	declare @maNguoiTimViec int,@maCV int
	
	select @maNguoiTimViec = MaNguoiTimViec
	from NGUOITIMVIEC
	where USER_ID = @userID;
	
	
	insert into CV(MaNguoiTimViec,NgayTao) values(@maNguoiTimViec,getdate());
	
	select @maCV = @@identity;
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	insert into CVUPLOAD(MaCV,Ten,DuongDan) values(@maCV,@tenCV,@duongDan);
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	insert into CV_GUI_NHATUYENDUNG values(@maNhaTuyenDung,@maCV,getdate(),1);
	
	if(@@error <> 0)
	begin
		set @flag = '0'
		rollback tran
		return
	end
	
	set @flag = '1';
end


------------ Lay CV UPload gui nha tuyen dung----------------------
create proc spLayCVUploadGuiNhaTuyenDung
	@userID int
as
begin
		declare @maNhaTuyenDung int;
		
		select @maNhaTuyenDung = MaNhaTuyenDung 
		from NHATUYENDUNG
		where USER_ID = @userID;
		
		select *
		from CV_GUI_NHATUYENDUNG cgn, CVUPLOAD cvu
		where cgn.MaCV = cvu.MaCV and cgn.MaNhaTuyenDung = @maNhaTuyenDung and cgn.TinhTrang = 1;
		
end

--store kiem tra duoc dang tin hay ko?

create proc spKiemTraDangTin
	@user_id int,
	@flag nvarchar(3) output
	
as
begin
	declare @manhatuyendung int
	--lay ma nha tuyen dung
	select @manhatuyendung = manhatuyendung
	from nhatuyendung
	where user_id = @user_id
	--xem nha tuyen dung co dong phi
	declare @dadongphi int,@ngaydongphi datetime
	select @dadongphi=count(*),@ngaydongphi = ngayketthuc
	from nhatuyendung_phi
	where manhatuyendung = @manhatuyendung and tinhtrang = 1
	
	if(@dadongphi = 0)
	begin
		set @flag = 0
		return
	end
	
	else
	begin
		declare @sotin int, @tongsotindadang int
		--lay so tin co the dang cua phi
		select @sotin = soluongtin
		from phi
		where manhatuyendung = @manhatuyendung and tinhtrang = 1
		
		--lay so tin da dang cua nha tuyen dung
		select @tongsotindadang = count(*)
		from congviec
		where ngaydang >= @ngaydongphi and manhatuyendung = @manhatuyendung
		
		if(@tongsotindadang >= @sotin)
		begin
			set @flag = 0
			return
		end
			
	end
	set @flag = 1
	return
end


----------------------------------------------------

alter PROCEDURE dbo.spCapNhatCVOnline
	@UserID nvarchar(10),
	@HoTen nvarchar(50),
	@GioiTinh bit,
	@Email nvarchar(50),
	@DienThoai nvarchar(50),
	@DiDong nvarchar(50),
	@MaTinhThanh int,
	@TrinhDo nvarchar(50),
	@NamKinhNghiem nvarchar(50),
	@DiaChi nvarchar(50),
	@MucLuong nvarchar(50),
	@ViTriMongMuon nvarchar(50),
	@GioiThieu nvarchar(max)
AS
begin
	declare @MaNguoiTimViec int
	declare @MaCV int
	select @MaNguoiTimViec = MaNguoiTimViec from NguoiTimViec where User_ID = @UserID
	select @MaCV = cvo.MaCV from CV cv, CVOnline cvo where cv.MaCV = cvo.MaCV and MaNguoiTimViec = @MaNguoiTimViec
	update CVOnline set HoTen = @HoTen, GioiTinh = @GioiTinh, Email = @Email, DienThoai = @DienThoai, DiDong = @Didong, DiaChi = @DiaChi, MaTinhThanh = @MaTinhThanh, TrinhDo = @TrinhDo, NamKinhNghiem = @NamKinhNghiem, MucLuong = @MucLuong, ViTriMongMuon = @ViTriMongMuon, GioiThieu = @GioiThieu where MaCV = @MaCV
	
	if(@@error <> 0)
	begin
		rollback tran
		RETURN		
	end
	
	
end





